<?php
/*
 * class ImporExcel
 * author gaoxueping 
 * version 2.0
 * @param string $host the destination host name
 * @param string $username uname of database server
 * @param string $password passwd of database
 * @param string $dbname  dbname
 * @param resource $conn  a resource of connection to server 
 * @return type
 */
    class ImporExcel{
        private $host;
        private $username;
        private $password;
        private $dbname;
        protected $conn;

        function __construct($dbinfo) {
            $this->dbinfo = func_get_arg(0);
            $this->host= $this->dbinfo['host'];
            $this->username = $this->dbinfo['user'];
            $this->password = $this->dbinfo['pass'];
            $this->dbname = $this->dbinfo['db'];
            $this->conn = new mysqli($this->host,$this->username,$this->password,$this->dbname);
            $this->conn->connect_errno?die($mysqli->error):"";
        }

        public function import($table,array $condition,array $fileds,array $title,$query = ''){
            if(trim($table)== '' || trim($table) == ''){
                die('table or query param cant empty all');
            }
            header('Content-type:application/vdn.ms-excel');
            header('Content-Disposition:attachment;filename=myexcel.xls');
            if(trim($query) == ''){
                $filed = join(",", $fileds);
                if(count($condition)>0){
                    foreach($condition as $key => $value){
                        $where[]= $key."=".$value;
                    }
                    $where = " WHERE ".join(' AND ', $where);
                }else{
                    $where = '';
                }
                $sql = trim("SELECT ".$filed." FROM ".$table.$where);
            }else{
                $sql = $query;
            }
            $result = $this->conn->query($sql);
            for($i=0;$i<count($title);$i++){
                $colum = ($i==(count($title)-1))?($title[$i]."\t\n"):($title[$i]."\t");
                echo iconv('utf-8', 'gb2312', $colum);
            }
            while($rows = $result->fetch_array()){
                for($i=0;$i<count($fileds);$i++){
                    $param = ($i==(count($fileds)-1))?($rows[$fileds[$i]]."\t\n"):($rows[$fileds[$i]]."\t");
                    echo $param;
                }
            }
    }
}
    $import = new ImporExcel(array('host'=>'localhost','user'=>'root','pass'=>'pass','db'=>'database'));
    $sql = "SELECT p.products_model,b.customers_basket_quantity,d.products_name,p.products_price,p.products_price*b.customers_basket_quantity AS sum_price,b.customers_basket_date_added FROM `zen_customers_basket` b JOIN `zen_products` p ON b.products_id = p.products_id JOIN zen_products_description d ON b.products_id = d.products_id WHERE b.customers_id = 28500 GROUP BY b.products_id";
    $import->import('zen_admin',array(),array('products_model','customers_basket_quantity','products_name','products_price','sum_price','customers_basket_date_added'),array('产品类型','数量','产品名称','单价','总价','添加日期'),$sql);
//    $sql = "SELECT * FROM `zen_customers_basket` b ,`zen_products` p ,`zen_products_description` d WHERE b.products_id = p.products_id AND b.products_id = d.products_id AND b.customers_id = 28500";
?>